package util.office;

import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import model.TStudent;
import model.TTeacherInfo;
import model.VCloudParentClass;
import model.VCloudParentStudent;

public class ReadExcel {

	
	public String getCellFormatValue(Cell cell) {

		String cellValue = "";
		if (cell == null) {
			return cellValue;
		}
		// 判断cell类型 getCellType()
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_NUMERIC: {
			// 获取单元格的值作为数字 getNumericCellValue()
			cellValue = String.valueOf((int) cell.getNumericCellValue());
			break;
		}
		case Cell.CELL_TYPE_FORMULA: {
			// 判断cell是否为日期格式
			if (DateUtil.isCellDateFormatted(cell)) {
				// 转换为日期格式YYYY-mm-dd
				SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
				Date d = cell.getDateCellValue();
				if (d != null) {
					cellValue = sdf.format(d);
				}
			} else {
				// 数字
				cellValue = String.valueOf(cell.getNumericCellValue());
			}
			break;
		}
		case Cell.CELL_TYPE_STRING: {
			cellValue = cell.getRichStringCellValue().getString();
			break;
		}
		}
		return cellValue;
	}

	/**
	 * 去读ParentExcel的方法readExcelForParent
	 * @param request
	 * @param response
	 * @return List<TParentInfo>
	 */
    public List readExcelForParent(HttpServletRequest request, HttpServletResponse response) {
       
    	String fileName = null;
    	List<VCloudParentClass> outerList = new ArrayList<VCloudParentClass>();  	
    	// 解析 创建上传文件对象
    	ServletFileUpload upload = new ServletFileUpload(new DiskFileItemFactory());   	
    	try {    		
    		List itemList = upload.parseRequest(request);    		
    		for (Iterator it = itemList.iterator(); it.hasNext();) {
				FileItem item = (FileItem) it.next();
				if (!item.isFormField())// 如果是非表单数据（没有放在表单里）
				{
					InputStream ins = item.getInputStream();
					//System.out.println(fileMap);

					Workbook wb = WorkbookFactory.create(ins);
					// 获取第一个sheet
					Sheet sheet = wb.getSheetAt(0);
					// 获取最大行数
					int rownum = sheet.getPhysicalNumberOfRows();
					// 获取第一行
					Row row = sheet.getRow(0);
					// 存放表中的数据
					// dataList = new ArrayList<WorkstationExcel>();
					// 循环行
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					for (int i = 1; i < rownum; i++) {
						// WorkstationExcel we = new WorkstationExcel();
						row = sheet.getRow(i);
						if (row != null) {
							VCloudParentClass vpc = new VCloudParentClass();   
							if (row.getCell(0) != null) {
								vpc.setRealName(getCellFormatValue(row.getCell(0)));
							}
							if (row.getCell(1) != null) {
								vpc.setSex(getCellFormatValue(row.getCell(1)));
							}
							if (row.getCell(2) != null) {
								vpc.setBirthday(sdf.format(HSSFDateUtil.getJavaDate(row.getCell(2).getNumericCellValue())));
							}
							if (row.getCell(3) != null) {
								vpc.setEmail(getCellFormatValue(row.getCell(3)));
							}
							if (row.getCell(4) != null) {
								vpc.setAddress(getCellFormatValue(row.getCell(4)));
							}  		                    
		                    if (row.getCell(5) != null) {
		                    	row.getCell(5).setCellType(Cell.CELL_TYPE_NUMERIC);
		                    	vpc.setPhone(new DecimalFormat("#").format(row.getCell(5).getNumericCellValue()));
							}	
		                    if (row.getCell(6) != null && !row.getCell(6).toString().equals("无")) {
		                    	row.getCell(6).setCellType(Cell.CELL_TYPE_NUMERIC);
		                    	vpc.setTell(new DecimalFormat("#").format(row.getCell(6).getNumericCellValue()));
							}
		                    else {
		                    	vpc.setTell(getCellFormatValue(row.getCell(6)));
							}
		                    if (row.getCell(7) != null && !row.getCell(7).toString().equals("无")) {
		                    	row.getCell(7).setCellType(Cell.CELL_TYPE_NUMERIC);
		                    	vpc.setWorkTel(new DecimalFormat("#").format(row.getCell(7).getNumericCellValue()));
							}
		                    else {
		                    	vpc.setWorkTel(getCellFormatValue(row.getCell(7)));
							}  
		                    if (row.getCell(8) != null) {
		                    	vpc.setWorkUnit(getCellFormatValue(row.getCell(8)));
		                    }
		                    if (row.getCell(9) != null) {
		                    	vpc.setWorkType(getCellFormatValue(row.getCell(9)));
		                    }
		                    if (row.getCell(10) != null) {
		                    	vpc.setEducation(getCellFormatValue(row.getCell(10)));
		                    }
		                    vpc.setActiveState(true);
		                    if (row.getCell(12) != null) {
		                    	vpc.setRoleid((int)row.getCell(12).getNumericCellValue());
		                    }
		                    if (row.getCell(13) != null) {
		                    	vpc.setSchoolid(getCellFormatValue(row.getCell(13)));
		                    } 
		                    if (row.getCell(14) != null) {
		                    	vpc.setClassid(getCellFormatValue(row.getCell(14)));
		                    } 
							outerList.add(vpc);						
						} 
						else {
							continue;
						}
					}
				}
			}
    		return outerList;
		} catch (Exception e) {
			e.printStackTrace();
		}
        return null;
    }

    /**
     * 去读TeacherExcel的方法readExcelForTeacher
     * @param request
     * @param response
     * @return List<TTeacherInfo>
     */
    public List readExcelForTeacher(HttpServletRequest request, HttpServletResponse response) {
    	String fileName = null;
    	List<TTeacherInfo> outerList = new ArrayList<TTeacherInfo>();  	
    	// 解析 创建上传文件对象
    	ServletFileUpload upload = new ServletFileUpload(new DiskFileItemFactory());   	
    	try {    		
    		List itemList = upload.parseRequest(request);    		
    		for (Iterator it = itemList.iterator(); it.hasNext();) {
				FileItem item = (FileItem) it.next();
				if (!item.isFormField())// 如果是非表单数据（没有放在表单里）
				{
					InputStream ins = item.getInputStream();
					//System.out.println(fileMap);

					Workbook wb = WorkbookFactory.create(ins);
					// 获取第一个sheet
					Sheet sheet = wb.getSheetAt(0);
					// 获取最大行数
					int rownum = sheet.getPhysicalNumberOfRows();
					// 获取第一行
					Row row = sheet.getRow(0);
					// 循环行
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					for (int i = 1; i < rownum; i++) {
						// WorkstationExcel we = new WorkstationExcel();
						row = sheet.getRow(i);
						if (row != null) {
							TTeacherInfo teacherInfo = new TTeacherInfo();
							teacherInfo.setRealName(getCellFormatValue(row.getCell(0)));
							teacherInfo.setSex(getCellFormatValue(row.getCell(1)));
							teacherInfo.setBirthday(sdf.format(HSSFDateUtil.getJavaDate(row.getCell(2).getNumericCellValue())));
							teacherInfo.setEmail(getCellFormatValue(row.getCell(3)));
							if (row.getCell(4) != null) {
		                    	row.getCell(4).setCellType(Cell.CELL_TYPE_NUMERIC);
		                    	teacherInfo.setPhone(new DecimalFormat("#").format(row.getCell(4).getNumericCellValue()));	
							}
							if (row.getCell(5) != null && !row.getCell(5).toString().equals("无")) {
		                    	row.getCell(5).setCellType(Cell.CELL_TYPE_NUMERIC);
		                    	teacherInfo.setTell(new DecimalFormat("#").format(row.getCell(5).getNumericCellValue()));
							}					
							else {
								teacherInfo.setTell(getCellFormatValue(row.getCell(5)));
							}
							if (row.getCell(6) != null  && !row.getCell(6).toString().equals("无")) {
		                    	row.getCell(6).setCellType(Cell.CELL_TYPE_NUMERIC);
		                    	teacherInfo.setWorkTel(new DecimalFormat("#").format(row.getCell(6).getNumericCellValue()));
							}					
							else {
								teacherInfo.setWorkTel(getCellFormatValue(row.getCell(6)));
							}					
							teacherInfo.setAddress(getCellFormatValue(row.getCell(7)));							
							teacherInfo.setPost(getCellFormatValue(row.getCell(8)));
							teacherInfo.setGraduateSchool(getCellFormatValue(row.getCell(9)));
							teacherInfo.setEntryTime(sdf.format(HSSFDateUtil.getJavaDate(row.getCell(10).getNumericCellValue())));							
							teacherInfo.setActiveState(true);				
							teacherInfo.setRoleid((int)row.getCell(12).getNumericCellValue());
							teacherInfo.setSchoolid(getCellFormatValue(row.getCell(13)));	                    
							outerList.add(teacherInfo);						
						} 
						else {
							continue;
						}
					}
				}
			}
    		return outerList;
		} catch (Exception e) {
			e.printStackTrace();
		}
        return null;
    }
    
    /**
     * 去读StudentExcel的方法readExcelForStudent
     * @param request
     * @param response
     * @return List<TStudent>
     */
    public List readExcelForStudent(HttpServletRequest request, HttpServletResponse response) {
    	String fileName = null;
    	List<TStudent> outerList = new ArrayList<TStudent>();  	
    	// 解析 创建上传文件对象
    	ServletFileUpload upload = new ServletFileUpload(new DiskFileItemFactory());   	
    	try {    		
    		List itemList = upload.parseRequest(request);    		
    		for (Iterator it = itemList.iterator(); it.hasNext();) {
				FileItem item = (FileItem) it.next();
				if (!item.isFormField())// 如果是非表单数据（没有放在表单里）
				{
					InputStream ins = item.getInputStream();
					//System.out.println(fileMap);

					Workbook wb = WorkbookFactory.create(ins);
					// 获取第一个sheet
					Sheet sheet = wb.getSheetAt(0);
					// 获取最大行数
					int rownum = sheet.getPhysicalNumberOfRows();
					// 获取第一行
					Row row = sheet.getRow(0);
					// 循环行
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					for (int i = 1; i < rownum; i++) {
						// WorkstationExcel we = new WorkstationExcel();
						row = sheet.getRow(i);
						if (row != null) {
							TStudent student = new TStudent();
//							if (row.getCell(0) != null) {
//		                    	row.getCell(0).setCellType(Cell.CELL_TYPE_NUMERIC);
//		                    	student.setStuid(new DecimalFormat("#").format(row.getCell(0).getNumericCellValue()));
//							}
							student.setStuid(row.getCell(0).getStringCellValue());
							student.setPwd(new DecimalFormat("#").format(row.getCell(1).getNumericCellValue())); 
							student.setRealName(getCellFormatValue(row.getCell(2)));
							student.setSex(getCellFormatValue(row.getCell(3)));
							student.setBirthday(sdf.format(HSSFDateUtil.parseYYYYMMDDDate(row.getCell(4).getStringCellValue())));
							student.setClassid(getCellFormatValue(row.getCell(5)));
							student.setSchoolid(getCellFormatValue(row.getCell(6)));
							outerList.add(student);						
						} 
						else {
							continue;
						}
					}
				}
			}
    		return outerList;
		} catch (Exception e) {
			e.printStackTrace();
		}
        return null;
    }
    
    /**
     * 去读ParStuExcel的方法readExcelForParStu
     * @param request
     * @param response
     * @param schoolid 学校id
     * @return List<TCloudParentStudent>
     */
    public List readExcelForParStu(HttpServletRequest request, HttpServletResponse response, String schoolid) {
    	String fileName = null;
    	List<VCloudParentStudent> outerList = new ArrayList<VCloudParentStudent>();  	
    	// 解析 创建上传文件对象
    	ServletFileUpload upload = new ServletFileUpload(new DiskFileItemFactory());   	
    	try {    		
    		List itemList = upload.parseRequest(request);    		
    		for (Iterator it = itemList.iterator(); it.hasNext();) {
				FileItem item = (FileItem) it.next();
				if (!item.isFormField())// 如果是非表单数据（没有放在表单里）
				{
					InputStream ins = item.getInputStream();
					//System.out.println(fileMap);

					Workbook wb = WorkbookFactory.create(ins);
					// 获取第一个sheet
					Sheet sheet = wb.getSheetAt(0);
					// 获取最大行数
					int rownum = sheet.getPhysicalNumberOfRows();
					// 获取第一行
					Row row = sheet.getRow(0);
					// 循环行
					for (int i = 1; i < rownum; i++) {
						row = sheet.getRow(i);
						if (row != null) {
							VCloudParentStudent parstu = new VCloudParentStudent();
							if (row.getCell(0) != null) {
		                    	row.getCell(0).setCellType(Cell.CELL_TYPE_NUMERIC);
		                    	parstu.setParentid((int)row.getCell(0).getNumericCellValue());
							}
							if (row.getCell(1) != null) {
		                    	parstu.setParUserName(row.getCell(1).getStringCellValue());
							}
							if (row.getCell(2) != null) {
		                    	parstu.setParRealName(row.getCell(2).getStringCellValue());
							}
							if (row.getCell(3) != null) {
		                    	parstu.setParSex(row.getCell(3).getStringCellValue());
							}
							if (row.getCell(4) != null) {
								row.getCell(4).setCellType(Cell.CELL_TYPE_NUMERIC);
		                    	parstu.setPhone(new DecimalFormat("#").format(row.getCell(4).getNumericCellValue()));
							}
							if (row.getCell(5) != null) {
								row.getCell(5).setCellType(Cell.CELL_TYPE_NUMERIC);
		                    	parstu.setStuid(new DecimalFormat("#").format(row.getCell(5).getNumericCellValue()));
							}
							if (row.getCell(6) != null) {
		                    	parstu.setStuRealName(row.getCell(6).getStringCellValue());
							}
							if (row.getCell(7) != null) {
		                    	parstu.setStuSex(row.getCell(7).getStringCellValue());
							}
							if (row.getCell(8) != null) {
		                    	parstu.setRelation(row.getCell(8).getStringCellValue());
							}
							parstu.setSchoolid(schoolid);
							outerList.add(parstu);						
						} 
						else {
							continue;
						}
					}
				}
			}
    		return outerList;
		} catch (Exception e) {
			e.printStackTrace();
		}
        return null;
    }
    
}
